package com.zyd.shiro.persistence.dao;

import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.zyd.shiro.persistence.beans.Goods;
import org.springframework.data.jpa.repository.Query;


import java.util.List;

/**
 * 数据访问接口
 *
 * @author Administrator
 */
public interface MallGoodsDao extends JpaRepository<Goods, Integer>, JpaSpecificationExecutor<Goods> {

    @Query(value = "select * from mall_goods  good where  good.is_delete =?1",nativeQuery = true)
    public List<Goods> getAllByIs_delete(Integer isDelete);



    @Query(value = "select * from  mall_goods good where  good.is_delete = ?1 order by id DESC",nativeQuery = true)
    public List<Goods> getAllByIs_deleteOrderByIdDESC( Integer isDelete);

    @Query(value = "select * from  mall_goods good where  good.is_delete = ?1 order by sales_initial DESC",nativeQuery = true)
    public List<Goods> getAllByIs_deleteOrderBySales_initialDesc(Integer isDelete);


    @Query(value = "select * from  mall_goods good where  good.category_id = ?1 and good.goods_name like '%?1%' ",nativeQuery = true)
    public List<Goods> getAllByCategory_idAndGoods_nameLike(Integer categoryId,String goodsName);

    @Query(value = "select * from  mall_goods good where  good.category_id = ?1 and good.goods_name like '%:nn%' ORDER BY (good.sales_initial+good.sales_actual) DESC ",nativeQuery = true)
    public List<Goods> getAllByCategory_idAndGoods_nameLikeOrderBySales_actualDesc(Integer categoryId,@Param("nn")String goodsName);


    @Query(value = "SELECT * FROM  mall_goods good WHERE  good.category_id = ?1 AND good.goods_name LIKE '%:nn%' ORDER BY (SELECT goods_price FROM mall_goods_spec WHERE goods_id = good.`id`) DESC",nativeQuery = true)
    public List<Goods> getAllByCategory_idAndGoods_nameLikeOrderBy(Integer categoryId,@Param("nn")String goodsName);





}
